部门表
    create table dept
    (
        DEPTNO int, -- 部门编号
        DNAME string,-- 部门名称
        LOC string -- 部门地址
    ) row format delimited
              fields terminated by ' ';

    dep.txt
    10 accounting new york
    20 research dallas
    30 sales chicago
    40 operations boston

员工表
    drop table emp;
    create table emp
    (
        EMPNO int, -- 员工id
        ENAME string, -- 员工姓名
        JOB string, -- 员工职位
        MGR int, -- 员工领导编号
        HIREDATE string,-- hire date雇佣日期
        SAL double,-- 员工薪水
        COMM int,-- 员工奖金
        DEPTNO int -- 部门编号
    )
    row format delimited
        fields terminated by ' ';

员工爱好表
    drop table emp_hobby;
    create table emp_hobby
    (
        empno int, -- 员工编号
        hobby array<string> -- 员工爱好

    )
    row format delimited
        fields terminated by ' '
        collection items terminated by ',';

员工薪水登记表sal grade
    create table salgrade (
       grade int, --等级
       losal int, -- 起始薪水
       hisal int-- 终止薪水
    )row format delimited
               fields terminated by ' ';

    salgrade.txt
    1 700 1200
    2 1201 1400
    3 1401 2000
    4 2001 3000
    5 3001 9999



emp.txt
7369 smith clerk 7902 17-12-1980  800 null 20
7499 allen salesman 7698 20-02-1981 1600 300 30
7521 ward salesman 7698 22-02-1981 1250 500 30
7566 jones manager 7839 02-04-1981 2975 null 20
7654 martin salesman 7698 28-09-1981 1250 1400 30
7698 blake manager 7839 01-05-1981 2850 null 30
7782 clark manager 7839 09-06-1981 2450 null 10
7788 scott analyst 7566 19-04-1987 3000 null 20
7839 king president null 17-11-1981 5000 null 10
7844 turner salesman 7698 08-09-1981 1500 0 30
7876 adams clerk 7788 23-05-1987 1100 null 20
7900 james clerk 7698 03-12-1981 950 null 30
7902 ford analyst 7566 03-12-1981 3000 null 20
7934 miller clerk 7782 23-01-1982 1300 null 10



emp_hobby.txt
7369 sing,run
7499 read,tea
7521 swimming
7566 mountain_climbing
8654 run
8698 tea







load data local inpath '/home/hadoop/data/empData/emp.txt' into table emp;
load data local inpath '/home/hadoop/data/empData/emp_hobby.txt' into table emp_hobby;
load data local inpath '/home/hadoop/data/empData/salgrade.txt' into table salgrade;
load data local inpath '/home/hadoop/data/empData/dep.txt' into table dept;


    in/not in:
        是否包含在集合汇总
    select ename ,sal from emp  where sal  in ( 800,1600,2000,1250);
    select ename,sal from emp  where sal not in ( 800,1600);


 聚合
    求和max()/最大值max()/最小值min()/平均avg()
    select max(sal) sal_max,deptno from emp;
    select min(sal) from emp;
    select avg(sal) from emp;

 分组
  group by
    求每个部门的平均薪水：
        select deptno , avg(sal) from emp group by deptno;
     预习：
        TODO group by 执行原理


    分组加条件查询

    select deptno , avg(sal) avg_sal  from text02.emp where deptno>10 group by deptno
        having avg_sal>2000 ;

    错误示例：分组之后补能用where 条件查询
        select deptno , avg(sal) avg_sal from text02.emp group by deptno
            where avg_sal>2000 ;


where 条件查询
    where 相当于Java中的if判断，返回true/false
    select * from emp;

    类似is null
    select comm from emp where comm<=>null;
    select comm from emp where comm is null;

    select comm from emp where comm is not null;

    <> !=  a<>b a!=b
    select deptno from emp where deptno<>20;
    select deptno from emp where deptno!=20;

    select * from emp where deptno<20;
    select * from emp where deptno>20;
    select * from emp where deptno<=20;
    select * from emp where deptno>=20;

    between 1600 and 3000 相当于 sal <=3000 and sal >=1600
    select sal from emp where sal between 1600 and 3000;
    select sal from emp where sal <=3000 and sal >=1600;


    select ename from emp where ename like '%S%';
    select ename from emp where ename like 'S%';
    一个下划线匹配一个字母
    select ename from emp where ename like 'S____';
    select ename from emp where ename like '%S';

    正则表达式
        .代表匹配所有字母
        *：量词，1个或者多个
    select * from emp where ename rlike '.*(IT|ONE).*';
    select * from emp where ename rlike '..(IT|ONE).*';


排序
    order by:全局排序
        select sal from emp order by sal desc;

    sort by：局部排序，分区排序
        select sal from emp_p_dynamic sort by sal desc;

    distribute by  sort by：
        distribute：分布
        rm任务中，指定按照哪个key进行分区，分完区再进行排序。

        select sal, deptno from emp
            distribute by deptno
            sort by sal, deptno;

         作业：分两个区
            sal deptno
            2000 10
            3000 10
            2000 30
            2000 50
            3000 10
            5000 10
            2000 10
            2000 20
            3000 40
            2000 60
            3000 20
            5000 10



case when then end
    类似Java中if else if  else
    使用if else语句类比理解

    select ename,sal,
        case
            when 0<sal and sal<=1000 then 'low'

            when 1000<sal and sal<=2000 then 'middle'
            else 'high'
        end as level
    from emp;

类型转换函数cast()
select sal from emp where sal > cast(950 as float) order by sal;

  连接查询
    join

    select ename , d.dname from emp as e
        join dept as d
            on e.deptno = d.deptno;

    select ename , d.dname from emp as e
        left join dept as d
            on e.deptno = d.deptno;

    select ename , d.dname from emp as e
            right join dept as d
                on e.deptno = d.deptno;


    select ename , d.dname ,h.hobby from emp as e
                left join dept as d
                    on e.deptno = d.deptno
                left join emp_hobby as h
                    on e.empno = h.empno
                    ;

    select ename , d.dname ,h.hobby from emp as e
                    left join dept as d
                        on e.deptno = d.deptno

                    left join emp_hobby as h
                        on e.empno = h.empno
                        ;


    select ename , d.dname ,h.hobby from emp as e
                        left join dept as d
                            on e.deptno = d.deptno

                        right join emp_hobby as h
                            on e.empno = h.empno
                            ;


    (1,2,3) join (5,6) = (1,5)(1,6)(2,5)(2,6)(3,5)(3,6)
    select e.* , d.dname from emp as e
            join dept d;

    union all

    todo 留画图作业